package com.beilang.base.util;

import com.beilang.hotline.telephone.model.TelephoneModel;
import com.beilang.hotline.telrecord.model.TelrecordModel;
import com.beilang.hotline.telrecord.to.SearchTO;
import com.beilang.hotline.telreply.model.TelreplyModel;
import jxl.Cell;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WriteException;

import java.math.BigDecimal;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class ReportExcelUtil {

    public void formatSheet1(jxl.write.WritableSheet ws) {
        for (int i = 0; i < 16; i++) {
            Cell c = ws.getCell(i, 0);
            String value = c.getContents();
            if (value != null && !"".equals(value)) {
                ws.setColumnView(i, 20);
            } else {
                return;
            }
        }
    }

    public int writeExcelVReportTable(SearchTO searchTO, int row, TelrecordModel telrecordModel, List list, jxl.write.WritableSheet ws) {

        ReportUtil reportUtil = new ReportUtil();
        int column = 0;
        if ("Y".equals(searchTO.getRecordTimeSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.RECORDTIME_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getRecordTimeStr()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getRecordTimeStr()), column, row, ws);
            }
            column = column + 1;
        }


        if ("Y".equals(searchTO.getCompanyIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.COMPANY_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getCompanyNameDesc()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getCompanyNameDesc()), column, row, ws);
            }
            column = column + 1;
        }
        if ("Y".equals(searchTO.getRecordIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.IDNO_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getRecordId()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getRecordId()), column, row, ws);
            }
            column = column + 1;
        }

        if ("Y".equals(searchTO.getPersonIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.NAME_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getPname()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getPname()), column, row, ws);
            }
            column = column + 1;


            if (row == 0) {
                addDescCell(ReportUtil.SEX_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getSexDesc()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getSexDesc()), column, row, ws);
            }
            column = column + 1;
        }

        if ("Y".equals(searchTO.getTelIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.RECORDNUMBER_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getRecordNumber()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getRecordNumber()), column, row, ws);
            }
            column = column + 1;

        }

        if ("Y".equals(searchTO.getSysIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.SYSTEM_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getSysNameDesc()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getSysNameDesc()), column, row, ws);
            }
            column = column + 1;

        }

        if ("Y".equals(searchTO.getModuleIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.MODULE_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getModuleNameDesc()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getModuleNameDesc()), column, row, ws);
            }
            column = column + 1;
        }

        if ("Y".equals(searchTO.getIssueIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.ISSUE_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getIssueNameDesc()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getIssueNameDesc()), column, row, ws);
            }
            column = column + 1;

        }

        if ("Y".equals(searchTO.getTitleIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.TITLE_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getTitleRemarks()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getTitleRemarks()), column, row, ws);
            }
            column = column + 1;
        }

        if ("Y".equals(searchTO.getRemarksIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.RECORDREMARKS_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getRecordRemarks()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getRecordRemarks()), column, row, ws);
            }
            column = column + 1;
        }

        if ("Y".equals(searchTO.getReasonIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.RECORDREASON_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getRecordReason()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getRecordReason()), column, row, ws);
            }
            column = column + 1;

        }

        if ("Y".equals(searchTO.getCreatorIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.CREATOR_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getCreatorNameDesc()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getCreatorNameDesc()), column, row, ws);
            }
            column = column + 1;
        }

        if ("Y".equals(searchTO.getStatusIdSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.STATUS_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getRecordStatusDesc()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getRecordStatusDesc()), column, row, ws);
            }
            column = column + 1;
        }

        if ("Y".equals(searchTO.getCurrentOwnerSelected())) {
            if (row == 0) {
                addDescCell(ReportUtil.CURRENTOIC_DESC, column, 0, ws);
                addCell(reportUtil.deNull(telrecordModel.getCurOwnerNameDesc()), column, 1, ws);
            } else {
                addCell(reportUtil.deNull(telrecordModel.getCurOwnerNameDesc()), column, row, ws);
            }
            column = column + 1;

        }
        if (row == 0) {
            row = row + 2;
        } else {
            row = row + 1;
        }

        if ("Y".equals(searchTO.getReplyIdSelected())) {
            row = addVListCell(list, row, ws);
        }

        return row;
    }

    private int addVListCell(List list, int row, jxl.write.WritableSheet ws) {
        ReportUtil reportUtil = new ReportUtil();
        if (list != null) {
            Iterator itor = list.iterator();
            int i = 1;
            while (itor.hasNext()) {
                TelreplyModel telreplyModel = (TelreplyModel) itor.next();
                String header = ReportUtil.REPLY_DES + i;
                try {
                    int lastColumn = getLastColumn(ws);
                    jxl.write.Label label = new jxl.write.Label(0, row, header, getHeader());
                    ws.addCell(label);
                    ws.mergeCells(0, row, lastColumn, row);
                    row = row + 1;
                    addCell(reportUtil.deNull(telreplyModel.getReplyByDesc()), 1, row, ws);
                    addDescCell(ReportUtil.REPLYBY_DESC, 0, row, ws);
                    ws.mergeCells(1, row, lastColumn, row);
                    row = row + 1;

                    addCell(reportUtil.deNull(telreplyModel.getReplyTimeStr()), 1, row, ws);
                    addDescCell(ReportUtil.REPLYTIME_DESC, 0, row, ws);
                    ws.mergeCells(1, row, lastColumn, row);
                    row = row + 1;

                    addCell(reportUtil.deNull(telreplyModel.getReplyContent()), 1, row, ws);
                    addDescCell(ReportUtil.REPLYCONTENT_DESC, 0, row, ws);
                    ws.mergeCells(1, row, lastColumn, row);
                    row = row + 1;
                    addCell(reportUtil.deNull(telreplyModel.getReason()), 1, row, ws);
                    addDescCell(ReportUtil.REPLYREASON_DESC, 0, row, ws);
                    ws.mergeCells(1, row, lastColumn, row);
                    row = row + 1;

                } catch (WriteException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
                i++;
            }
        }
        return row;
    }


    public int writeExcelHReportTable(SearchTO searchTO, int row, TelrecordModel telrecordModel, List list, jxl.write.WritableSheet ws) {
        ReportUtil reportUtil = new ReportUtil();
        if ("Y".equals(searchTO.getRecordTimeSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getRecordTimeStr()), 1, row, ws);
            addDescCell(ReportUtil.RECORDTIME_DESC, 0, row, ws);
            row = row + 1;
        }
        if ("Y".equals(searchTO.getCompanyIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getCompanyNameDesc()), 1, row, ws);
            addDescCell(ReportUtil.COMPANY_DESC, 0, row, ws);
            row = row + 1;
        }

        if ("Y".equals(searchTO.getRecordIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getRecordId()), 1, row, ws);
            addDescCell(ReportUtil.IDNO_DESC, 0, row, ws);
            row = row + 1;
        }
        if ("Y".equals(searchTO.getPersonIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getPname()), 1, row, ws);
            addDescCell(ReportUtil.NAME_DESC, 0, row, ws);
            row = row + 1;


            addCell(reportUtil.deNull(telrecordModel.getSexDesc()), 1, row, ws);
            addDescCell(ReportUtil.SEX_DESC, 0, row, ws);
            row = row + 1;

        }

        if ("Y".equals(searchTO.getTelIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getRecordNumber()), 1, row, ws);
            addDescCell(ReportUtil.RECORDNUMBER_DESC, 0, row, ws);
            row = row + 1;
        }
        if ("Y".equals(searchTO.getSysIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getSysNameDesc()), 1, row, ws);
            addDescCell(ReportUtil.SYSTEM_DESC, 0, row, ws);
            row = row + 1;

        }
        if ("Y".equals(searchTO.getModuleIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getModuleNameDesc()), 1, row, ws);
            addDescCell(ReportUtil.MODULE_DESC, 0, row, ws);
            row = row + 1;

        }
        if ("Y".equals(searchTO.getIssueIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getIssueNameDesc()), 1, row, ws);
            addDescCell(ReportUtil.ISSUE_DESC, 0, row, ws);
            row = row + 1;
        }
        if ("Y".equals(searchTO.getTitleIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getTitleRemarks()), 1, row, ws);
            addDescCell(ReportUtil.TITLE_DESC, 0, row, ws);
            row = row + 1;
        }
        if ("Y".equals(searchTO.getRemarksIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getRecordRemarks()), 1, row, ws);
            addDescCell(ReportUtil.RECORDREMARKS_DESC, 0, row, ws);
            row = row + 1;
        }
        if ("Y".equals(searchTO.getReasonIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getRecordReason()), 1, row, ws);
            addDescCell(ReportUtil.RECORDREASON_DESC, 0, row, ws);
            row = row + 1;

        }
        if ("Y".equals(searchTO.getCreatorIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getCreatorNameDesc()), 1, row, ws);
            addDescCell(ReportUtil.CREATOR_DESC, 0, row, ws);
            row = row + 1;
        }
        if ("Y".equals(searchTO.getStatusIdSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getRecordStatusDesc()), 1, row, ws);
            addDescCell(ReportUtil.STATUS_DESC, 0, row, ws);
            row = row + 1;

        }
        if ("Y".equals(searchTO.getCurrentOwnerSelected())) {
            addCell(reportUtil.deNull(telrecordModel.getCurOwnerNameDesc()), 1, row, ws);
            addDescCell(ReportUtil.CURRENTOIC_DESC, 0, row, ws);
            row = row + 1;
        }
//

        if ("Y".equals(searchTO.getReplyIdSelected())) {
            row = addVListCell(list, row, ws);
        }
        return row;
    }

    public void formatSheet(jxl.write.WritableSheet ws) {
        ws.setColumnView(0, 20);
        ws.setColumnView(1, 115);
    }

    private boolean addDescCell(String desc, int row, int column, jxl.write.WritableSheet ws) {
        try {
            jxl.write.Label label = new jxl.write.Label(row, column, desc, getHeader());
            ws.addCell(label);
            return true;
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        return false;
    }

    private void addCell(String value, int row, int column, jxl.write.WritableSheet ws) {
        try {
            jxl.write.Label label = new jxl.write.Label(row, column, value, getCell());
            ws.addCell(label);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    public WritableCellFormat getHeader() {
        WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);//定义字体
        try {
            font.setColour(Colour.BLACK);//黑色字体
        } catch (WriteException e1) {
            e1.printStackTrace();
        }
        WritableCellFormat format = new WritableCellFormat(font);
        try {
            format.setAlignment(jxl.format.Alignment.LEFT);//居左
            format.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//上下居上
            format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);//黑色边框
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    public WritableCellFormat getHeaderCenter() {
        WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);//定义字体
        try {
            font.setColour(Colour.BLACK);//黑色字体
        } catch (WriteException e1) {
            e1.printStackTrace();
        }
        WritableCellFormat format = new WritableCellFormat(font);
        try {
            format.setAlignment(jxl.format.Alignment.CENTRE);//居中
            format.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//上下居上
            format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);//黑色边框
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    public WritableCellFormat getCell() {//12号字体,上下左右居中,带黑色边框

        WritableFont font = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD);

        WritableCellFormat format = new WritableCellFormat(font);

        try {
            format.setAlignment(jxl.format.Alignment.LEFT);
            format.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);
            format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);//黑色边框
            format.setWrap(true);
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    private int getLastColumn(jxl.write.WritableSheet ws) {
        for (int i = 0; i < 16; i++) {
            Cell c = ws.getCell(i, 0);
            String value = c.getContents();
            if (value == null || "".equals(value)) {
                return i - 1;
            }
        }
        return 0;
    }

    public void writeUserNumberRptHeader(Date startDate, Date endDate, jxl.write.WritableSheet ws, int workDays) {
        try {
            jxl.write.Label label1 = new jxl.write.Label(0, 0, "开始时间:" + DateUtil.formatDate(startDate), getCell());
            jxl.write.Label label2 = new jxl.write.Label(0, 1, "结束时间:" + DateUtil.formatDate(endDate), getCell());
            jxl.write.Label label3 = new jxl.write.Label(3, 0, "共计" + workDays + "个工作日", getCell());

            ws.addCell(label1);
            ws.addCell(label2);
            ws.addCell(label3);

            ws.mergeCells(0, 0, 2, 0);
            ws.mergeCells(0, 1, 2, 1);
            ws.mergeCells(3, 0, 4, 1);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    public void writeUserNumberRptDetail(jxl.write.WritableSheet ws, List list, String type, int workDays) {
        long previousDeptId = 0;
        long currentDeptId = 0;

        int row = 3;
        long totalDuration = 0;
        int totalRecordCount = 0;
        int i = 1;
        if (list != null && !list.isEmpty()) {
            Iterator itor = list.iterator();
            while (itor.hasNext()) {
                TelephoneModel model = (TelephoneModel) itor.next();
                currentDeptId = model.getDeptId();
                try {
                    if (currentDeptId != previousDeptId) {
                        if (totalDuration != 0 && totalRecordCount != 0) {
//                            if ("T".equals(type)) {
                                row = writeTotal(ws, row, totalDuration, totalRecordCount, type, workDays, i - 1);
//                            }
                            totalDuration = 0;
                            totalRecordCount = 0;
                            i = 1;
                        }

                        jxl.write.Label label1 = new jxl.write.Label(0, row, model.getDeptName(), getHeaderCenter());
                        ws.addCell(label1);
                        ws.mergeCells(0, row, 2, row);

                        row = row + 1;
                        String header = "";
                        if ("U".equals(type)) {
                            header = "用户";
                        } else if ("T".equals(type)) {
                            header = "电话";
                        }
                        jxl.write.Label label2 = new jxl.write.Label(0, row, header, getHeader());
                        jxl.write.Label label3 = new jxl.write.Label(1, row, "时间", getHeader());
                        jxl.write.Label label4 = new jxl.write.Label(2, row, "个数", getHeader());
                        ws.addCell(label2);
                        ws.addCell(label3);
                        ws.addCell(label4);
                        row = row + 1;
                    }

                    jxl.write.Label label5 = null;

                    if ("U".equals(type)) {
                        label5 = new jxl.write.Label(0, row, StringUtil.splitString(model.getUserName()), getCell());
                    } else if ("T".equals(type)) {
                        label5 = new jxl.write.Label(0, row, model.getUserNumber(), getCell());
                    }

                    jxl.write.Label label6 = new jxl.write.Label(1, row, getFileTimeDesc(model.getDuration()), getCell());
                    totalDuration = totalDuration + model.getDuration();
                    jxl.write.Label label7 = new jxl.write.Label(2, row, String.valueOf(model.getRecordCount()), getCell());
                    totalRecordCount = totalRecordCount + model.getRecordCount();
                    ws.addCell(label5);
                    ws.addCell(label6);
                    ws.addCell(label7);
                } catch (WriteException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }

                row = row + 1;
                previousDeptId = currentDeptId;
                i++;
            }
        }

        if (totalDuration != 0 && totalRecordCount != 0) {
//            if ("T".equals(type)) {
                row = writeTotal(ws, row, totalDuration, totalRecordCount, type, workDays, i - 1);
//            }
        }

        formatUserNumberRptDetail(ws);
    }

    public int writeTotal(jxl.write.WritableSheet ws, int row, long totalDuration, int totalRecordCount, String type, int workDays, int i) {
        try {
            jxl.write.Label label8 = new jxl.write.Label(0, row, "总计", getCell());
            jxl.write.Label label9 = new jxl.write.Label(1, row, getFileTimeDesc(totalDuration), getCell());
            jxl.write.Label label10 = new jxl.write.Label(2, row, String.valueOf(totalRecordCount), getCell());
            ws.addCell(label8);
            ws.addCell(label9);
            ws.addCell(label10);
            row = row + 1;


            jxl.write.Label label11 = new jxl.write.Label(0, row, "总计", getCell());
            BigDecimal totalHours = getFileTimeDescHour(totalDuration);
            String strTotalHours = totalHours.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
            jxl.write.Label label12 = new jxl.write.Label(1, row, StringUtil.deNull(strTotalHours), getCell());
            jxl.write.Label label13 = new jxl.write.Label(2, row, "时", getCell());
            row = row + 1;
            jxl.write.Label label14 = new jxl.write.Label(0, row, "总计", getCell());
            BigDecimal totalMins = getFileTimeDescMin(totalDuration);
            String strTotalMins = totalMins.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
            jxl.write.Label label15 = new jxl.write.Label(1, row, strTotalMins, getCell());
            jxl.write.Label label16 = new jxl.write.Label(2, row, "分", getCell());

            ws.addCell(label11);
            ws.addCell(label12);
            ws.addCell(label13);

            ws.addCell(label14);
            ws.addCell(label15);
            ws.addCell(label16);

            row = row + 1;
            if ("T".equals(type))
            {
            String eveyDayHour = totalHours.divide(new BigDecimal(workDays), 2, BigDecimal.ROUND_HALF_UP).toString();
            jxl.write.Label label17 = new jxl.write.Label(0, row, "平均每天：", getCell());
            jxl.write.Label label18 = new jxl.write.Label(1, row, eveyDayHour, getCell());
            jxl.write.Label label19 = new jxl.write.Label(2, row, "时", getCell());

            row = row + 1;
            String everyDayHourPhone = new BigDecimal(eveyDayHour).divide(new BigDecimal(i), 2, BigDecimal.ROUND_HALF_UP).toString();
            jxl.write.Label label20 = new jxl.write.Label(0, row, "平均每天每部电话：", getCell());
            jxl.write.Label label21 = new jxl.write.Label(1, row, everyDayHourPhone, getCell());
            jxl.write.Label label22 = new jxl.write.Label(2, row, "时", getCell());
            row = row + 1;


            String eveyDayPhone = new BigDecimal(totalRecordCount).divide(new BigDecimal(workDays), 2, BigDecimal.ROUND_HALF_UP).toString();
            jxl.write.Label label23 = new jxl.write.Label(0, row, "平均每天电话：", getCell());
            jxl.write.Label label24 = new jxl.write.Label(1, row, eveyDayPhone, getCell());
            jxl.write.Label label25 = new jxl.write.Label(2, row, "个", getCell());


            row = row + 1;
            String eveyDayUser = new BigDecimal(totalRecordCount).divide(new BigDecimal(workDays), 2, BigDecimal.ROUND_HALF_UP).divide(new BigDecimal(i), 2, BigDecimal.ROUND_HALF_UP).toString();
            jxl.write.Label label29 = new jxl.write.Label(0, row, "平均每天每人：", getCell());
            jxl.write.Label label30 = new jxl.write.Label(1, row, eveyDayUser, getCell());
            jxl.write.Label label31 = new jxl.write.Label(2, row, "个", getCell());

            row = row + 1;
            String eveyDayUserHour = new BigDecimal(totalRecordCount).divide(new BigDecimal(workDays), 2, BigDecimal.ROUND_HALF_UP).
                    divide(new BigDecimal(i), 2, BigDecimal.ROUND_HALF_UP).
                    divide(new BigDecimal(7.5), 2, BigDecimal.ROUND_HALF_UP).toString();
            jxl.write.Label label32 = new jxl.write.Label(0, row, "平均每天每人每小时：", getCell());
            jxl.write.Label label33 = new jxl.write.Label(1, row, eveyDayUserHour, getCell());
            jxl.write.Label label34 = new jxl.write.Label(2, row, "个", getCell());


            row = row + 1;
            String everyNumberTime = totalMins.divide(new BigDecimal(totalRecordCount), 2, BigDecimal.ROUND_HALF_UP).toString();
            jxl.write.Label label35 = new jxl.write.Label(0, row, "平均每个电话时长：", getCell());
            jxl.write.Label label36 = new jxl.write.Label(1, row, everyNumberTime, getCell());
            jxl.write.Label label37 = new jxl.write.Label(2, row, "分", getCell());

            ws.addCell(label17);
            ws.addCell(label18);
            ws.addCell(label19);

            ws.addCell(label20);
            ws.addCell(label21);
            ws.addCell(label22);


            ws.addCell(label23);
            ws.addCell(label24);
            ws.addCell(label25);

            ws.addCell(label35);
            ws.addCell(label36);
            ws.addCell(label37);

            ws.addCell(label29);
            ws.addCell(label30);
            ws.addCell(label31);

            ws.addCell(label32);
            ws.addCell(label33);
            ws.addCell(label34);
            }
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        return row + 1;
    }

    public void formatUserNumberRptDetail(jxl.write.WritableSheet ws) {
        for (int i = 0; i < 3; i++) {
            Cell c = ws.getCell(i, 0);
            ws.setColumnView(i, 20);
        }
    }

    public void writeTelHistoryRptHeader(jxl.write.WritableSheet ws) {
        try {
            jxl.write.Label label1 = new jxl.write.Label(0, 0, "本机号码", getHeader());
            jxl.write.Label label2 = new jxl.write.Label(1, 0, "名称", getHeader());
            jxl.write.Label label3 = new jxl.write.Label(2, 0, "呼入号码", getHeader());
            jxl.write.Label label4 = new jxl.write.Label(3, 0, "呼出号码", getHeader());
            jxl.write.Label label5 = new jxl.write.Label(4, 0, "录音起始时间", getHeader());
            jxl.write.Label label6 = new jxl.write.Label(5, 0, "通话时长", getHeader());
            jxl.write.Label label7 = new jxl.write.Label(6, 0, "文件路径", getHeader());
            jxl.write.Label label8 = new jxl.write.Label(7, 0, "文件名称", getHeader());

            ws.addCell(label1);
            ws.addCell(label2);
            ws.addCell(label3);
            ws.addCell(label4);
            ws.addCell(label5);
            ws.addCell(label6);
            ws.addCell(label7);
            ws.addCell(label8);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }


    }

    public void writeTelHistoryRptDetail(jxl.write.WritableSheet ws, TelephoneModel telephoneModel, int row) {
        try {
            jxl.write.Label label1 = new jxl.write.Label(0, row, telephoneModel.getUserNumber(), getCell());
            jxl.write.Label label2 = new jxl.write.Label(1, row, StringUtil.splitString(telephoneModel.getUserName()), getCell());

            jxl.write.Label label3 = null;
            jxl.write.Label label4 = null;
            if (Constant.IN.equals(telephoneModel.getType())) {
                label3 = new jxl.write.Label(2, row, telephoneModel.getTelephoneNumber(), getCell());
                label4 = new jxl.write.Label(3, row, " ", getCell());
            } else if (Constant.OUT.equals(telephoneModel.getType())) {
                label3 = new jxl.write.Label(2, row, " ", getCell());
                label4 = new jxl.write.Label(3, row, telephoneModel.getTelephoneNumber(), getCell());
            } else {
                label3 = new jxl.write.Label(2, row, " ", getCell());
                label4 = new jxl.write.Label(3, row, " ", getCell());
            }

            jxl.write.Label label5 = new jxl.write.Label(4, row, DateUtil.formatDateHHMM(telephoneModel.getStartTime()), getCell());
            jxl.write.Label label6 = new jxl.write.Label(5, row, getFileTimeDesc(telephoneModel.getDuration()), getCell());
            String serverPath = telephoneModel.getServerName() + "\\" + telephoneModel.getFilePath();
            jxl.write.Label label7 = new jxl.write.Label(6, row, serverPath, getCell());
            jxl.write.Label label8 = new jxl.write.Label(7, row, telephoneModel.getFileName(), getCell());

            ws.addCell(label1);
            ws.addCell(label2);
            ws.addCell(label3);
            ws.addCell(label4);
            ws.addCell(label5);
            ws.addCell(label6);
            ws.addCell(label7);
            ws.addCell(label8);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    public void formatTelHistoryRptDetailSheet(jxl.write.WritableSheet ws) {
        for (int i = 0; i < 8; i++) {
            Cell c = ws.getCell(i, 0);
            String value = c.getContents();
            if (i == 6) {
                ws.setColumnView(i, 50);
            } else if (i == 4) {
                ws.setColumnView(i, 20);
            } else {
                ws.setColumnView(i, 15);
            }

        }
    }

    public void writeUserTelRptHeader(jxl.write.WritableSheet ws, String type, Date dateFrom, Date dateTo) {
        try {
            jxl.write.Label label1 = new jxl.write.Label(0, 0, "开始时间:" + DateUtil.formatDate(dateFrom), getHeader());
            ws.mergeCells(0, 0, 2, 0);
            jxl.write.Label label3 = new jxl.write.Label(0, 1, "结束时间:" + DateUtil.formatDate(dateTo), getHeader());
            ws.mergeCells(0, 1, 2, 1);
            String title = "";
            if ("U".equals(type)) {
                title = "用户";
            } else if ("T".equals(type)) {
                title = "电话";
            }
            jxl.write.Label label5 = new jxl.write.Label(0, 2, title, getHeader());
            jxl.write.Label label6 = new jxl.write.Label(1, 2, "时间", getHeader());
            jxl.write.Label label7 = new jxl.write.Label(2, 2, "个数", getHeader());


            ws.addCell(label1);
            ws.addCell(label3);
            ws.addCell(label5);
            ws.addCell(label6);
            ws.addCell(label7);

        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    public void writeUserTelRptDetial(jxl.write.WritableSheet ws, int row, String col1, String col2, String col3) {
        try {
            jxl.write.Label label1 = new jxl.write.Label(0, row, StringUtil.deNull(col1), getCell());
            jxl.write.Label label2 = new jxl.write.Label(1, row, StringUtil.deNull(col2), getCell());
            jxl.write.Label label3 = new jxl.write.Label(2, row, StringUtil.deNull(col3), getCell());

            ws.addCell(label1);
            ws.addCell(label2);
            ws.addCell(label3);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    public BigDecimal getFileTimeDescHour(long time) {
        int between = (int) time;
        long day1 = between / (24 * 3600);
        long hour1 = between % (24 * 3600) / 3600;
        long minute1 = between % 3600 / 60;
        long second1 = between % 60;


        long x5 = (day1 * 24 + hour1);
        //long x3 = minute1;
        // long x1 = second1;

        BigDecimal result = new BigDecimal(0.00);

        if (x5 != 0) {
            BigDecimal big1 = new BigDecimal(String.valueOf(x5));
            BigDecimal big2 = new BigDecimal(String.valueOf(minute1)).divide(new BigDecimal("60"), 10, BigDecimal.ROUND_HALF_UP);
            BigDecimal big3 = new BigDecimal(String.valueOf(second1)).divide(new BigDecimal("3600"), 10, BigDecimal.ROUND_HALF_UP);

            result = big1.add(big2).add(big3);
        } else if (minute1 != 0) {
            BigDecimal big2 = new BigDecimal(String.valueOf(minute1)).divide(new BigDecimal("60"), 10, BigDecimal.ROUND_HALF_UP);
            BigDecimal big3 = new BigDecimal(String.valueOf(second1)).divide(new BigDecimal("3600"), 10, BigDecimal.ROUND_HALF_UP);

            result = big2.add(big3);
        } else if (second1 != 0) {
            BigDecimal big3 = new BigDecimal(String.valueOf(second1)).divide(new BigDecimal("3600"), 10, BigDecimal.ROUND_HALF_UP);

            result = big3;
        }
        return result;
    }

    public BigDecimal getFileTimeDescMin(long time) {
        int between = (int) time;
        long day1 = between / (24 * 3600);
        long hour1 = between % (24 * 3600) / 3600;
        long minute1 = between % 3600 / 60;
        long second1 = between % 60;


        long x5 = (day1 * 24 + hour1);
        //long x3 = minute1;
        // long x1 = second1;

        BigDecimal result = new BigDecimal(0.00);
        if (x5 != 0) {
            BigDecimal big1 = new BigDecimal(String.valueOf(x5 * 60));
            BigDecimal big2 = new BigDecimal(String.valueOf(minute1));
            BigDecimal big3 = new BigDecimal(second1).divide(new BigDecimal("60"), 10, BigDecimal.ROUND_HALF_UP);
            result = big1.add(big2).add(big3);

        } else if (minute1 != 0) {
            BigDecimal big2 = new BigDecimal(String.valueOf(minute1));
            BigDecimal big3 = new BigDecimal(second1).divide(new BigDecimal("60"), 10, BigDecimal.ROUND_HALF_UP);
            result = big2.add(big3);
        } else if (second1 != 0) {
            BigDecimal big3 = new BigDecimal(second1).divide(new BigDecimal("60"), 10, BigDecimal.ROUND_HALF_UP);
            result = big3;
        }
        return result;
    }

    public String getFileTimeDesc(long time) {
        int between = (int) time;
        long day1 = between / (24 * 3600);
        long hour1 = between % (24 * 3600) / 3600;
        long minute1 = between % 3600 / 60;
        long second1 = between % 60;


        long x5 = (day1 * 24 + hour1);
        //long x3 = minute1;
        // long x1 = second1;

        String result = "";
        if (x5 != 0) {
            result = x5 + "小时" + minute1 + "分" + second1 + "秒";
        } else if (minute1 != 0) {
            result = minute1 + "分" + second1 + "秒";
        } else if (second1 != 0) {
            result = second1 + "秒";
        }


        return result;
    }


}
